package com.dx.mes.service.process.service;

import com.dx.common.table.ppt.Bis_mtrl;
import com.dx.common.table.ppt.Ret_crt_no;
import com.dx.common.table.ppt.Ret_crt_noId;
import com.dx.common.table.ppt.Ret_prd_in;
import com.dx.common.table.ppt.Ret_wo;
import com.dx.mes.common.dao.IBisMtrlDao;
import com.dx.mes.common.dao.IRetBoxDao;
import com.dx.mes.common.dao.IRetCrtNoDao;
import com.dx.mes.common.dao.IRetPrdInDao;
import com.dx.mes.common.dao.IRetWODao;
import com.dx.mes.common.table.rtnBean.SimpleRtnBeen;
import com.dx.mes.common.transcoder.util.AppContext;
import com.dx.mes.common.transcoder.util.JacksonUtil;
import com.dx.mes.common.transcoder.util.LogUtils;
import com.dx.mes.common.transcoder.util.Util;
import com.dx.mes.facade.process.service.IFppwhsopeService;
import com.dx.mes.facade.process.tx.Fppwhsope.FppwhsopeI;
import com.dx.mes.facade.process.tx.Fppwhsope.FppwhsopeIA;
import com.dx.mes.facade.process.tx.Fppwhsope.FppwhsopeO;
import com.dx.mes.facade.process.tx.Fppwhsope.FppwhsopeOA;
import com.dx.mes.facade.process.tx.Fppwhsope.FppwhsopeOB;

import static com.dx.mes.common.bean.def.GenericDef._NORMAL;
import static com.dx.mes.common.bean.def.GenericDef.E_FPPWHSOPE_INVALID_BOX_STAT;
import static com.dx.mes.common.bean.def.GenericDef.EVT_DENSEBOX_RETURN_TO_WH;
import static com.dx.mes.common.bean.def.GenericDef.ACTION_FLG_ADD;
import static com.dx.mes.common.bean.def.GenericDef.ACTION_FLG_DELETE;
import static com.dx.mes.common.bean.def.GenericDef.ACTION_FLG_LIST_WHOLE;
import static com.dx.mes.common.bean.def.GenericDef.ACTION_FLG_QUERY;
import static com.dx.mes.common.bean.def.GenericDef.ACTION_FLG_UPDATE;
import static com.dx.mes.common.bean.def.GenericDef.E_FPPWHSOPE_INVALID_INPUT;
import static com.dx.mes.common.bean.def.GenericDef.RETURN_CODE_OK;
import static com.dx.mes.common.bean.def.GenericDef.RETURN_CODE_SET_ERROR;
import static com.dx.mes.common.bean.def.GenericDef.RETURN_CODE_UNKNOWN;
import static com.dx.mes.common.bean.def.GenericDef.RETURN_MESG_OK;
import static com.dx.mes.common.bean.def.GenericDef.TRX_OUT;
import static com.dx.mes.common.bean.def.GenericDef.E_RET_CUS_PRD_IN;
import static com.dx.mes.common.bean.def.GenericDef.E_DELETE_NOT_FOUND;
import static com.dx.mes.common.bean.def.GenericDef.E_FDPAPLYWO_OTHER_ERROR;
import static com.dx.mes.common.bean.def.GenericDef.E_FPPWHSOPE;
import static com.dx.mes.common.bean.def.GenericDef.E_ADD_EXIST;
import static com.dx.mes.common.bean.def.GenericDef.E_FPPWHSOPE_BISMTRL_NOT_FOUND;
import static com.dx.mes.common.bean.def.GenericDef.EVT_DENSEBOX_GI;
import static com.dx.mes.common.bean.def.GenericDef.EVT_DENSEBOX_RETURN_TO_CUS;
import static com.dx.mes.common.bean.def.GenericDef.EVT_DENSEBOX_SCRAP;
import static com.dx.mes.common.bean.def.GenericDef.DENSE_STAT_OUT       ;
import static com.dx.mes.common.bean.def.GenericDef.DENSE_STAT_RTCS      ;
import static com.dx.mes.common.bean.def.GenericDef.DENSE_STAT_SCRP      ;
import static com.dx.mes.common.bean.def.GenericDef.DENSE_STAT_IN_PROCESS;
import static com.dx.mes.common.bean.def.GenericDef.LOT_TYPE;
import static com.dx.mes.common.bean.def.GenericDef.ACTION_FLG_FIFO;
import static com.dx.mes.common.bean.def.GenericDef._SPACE;
import static com.dx.mes.common.bean.def.GenericDef.E_RET_PRD_IN;
import static com.dx.mes.common.bean.def.GenericDef.E_READ_NOT_FOUND;
import static com.dx.mes.common.bean.def.GenericDef.DENSEBOX_CATE;
import static com.dx.mes.common.bean.def.GenericDef.E_FPPWHSOPE_BOX_CATE_INVALID;
import static com.dx.mes.common.bean.def.GenericDef.E_FPPWHSOPE_MTRL_BOX_FIFO;



import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collector;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.dx.mes.common.bean.def.GenericDef.T_FPPWHSOPE;

import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

@Service("fppwhsopeService")
public class FppwhsopeService implements IFppwhsopeService{
	private LogUtils logUtils;
	private static String TX_NAME = T_FPPWHSOPE;
	
	@Autowired
	private IRetPrdInDao retPrdInDao;
	@Autowired
	private IRetBoxDao retBoxDao;
	@Autowired
	private IBisMtrlDao bisMtrlDao;
	@Autowired
	private IRetCrtNoDao retCrtNoDao;
	@Autowired
	private IRetWODao retWODao;
	

	@Override
	public String subMainProc(String evt_no, String strInMsg) {
		AppContext.clear();
		AppContext.setCurrServiceName(FppwhsopeService.class.getSimpleName());
		AppContext.setCurrEventNumber(evt_no);
		logUtils = new LogUtils(FppwhsopeService.class);
		logUtils.info("[InTrx:" + strInMsg + "]");
		
		Session session = retPrdInDao.getCrSession();
		long rtnCode = _NORMAL;
		String outMsg = null;

		FppwhsopeI inTrx = new FppwhsopeI();
		FppwhsopeO outTrx = new FppwhsopeO();
		outTrx.setTrx_id(TX_NAME);
		outTrx.setRtn_code(String.valueOf(RETURN_CODE_SET_ERROR));
		outTrx.setType_id(TRX_OUT);


		try {
			inTrx = JacksonUtil.fromJson(strInMsg, FppwhsopeI.class);

			rtnCode = subMainProc2(inTrx, outTrx);
			if (rtnCode != _NORMAL) {
				TransactionAspectSupport.currentTransactionStatus()
						.setRollbackOnly();
				outTrx.setRtn_code(String.valueOf(rtnCode));
			} else {
				outTrx.setRtn_code(String.valueOf(RETURN_CODE_OK));
				outTrx.setRtn_mesg(RETURN_MESG_OK);
				session.flush();
			}
		} catch (Exception ex) {
			TransactionAspectSupport.currentTransactionStatus()
					.setRollbackOnly();
			outTrx.setRtn_mesg(ex.toString());
			outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
			logUtils.error(Util.stackTraceToString(ex));
		} finally {
			outMsg = JacksonUtil.toJSONStr(outTrx);
			logUtils.info("[OutTrx:" + outMsg + "]");
		}
		return outMsg;
	}
	
	/**
	 * Real action type
	 * 
	 * @param
	 * @return
	 */
	private long subMainProc2(FppwhsopeI inTrx, FppwhsopeO outTrx) {
		long lRc = _NORMAL;
		char actionType = inTrx.getAction_flg().toCharArray()[0];
		Timestamp gEvtTimestamp = Util.getCurrentTimestamp();
		switch (actionType) {
		case ACTION_FLG_ADD: {
			lRc = panelMaterialReceive(inTrx, outTrx, gEvtTimestamp);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case ACTION_FLG_UPDATE: {
			lRc = updatePanelMtrlRecord(inTrx, outTrx, gEvtTimestamp);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case ACTION_FLG_QUERY: {
			lRc = queryPanelMaterial(inTrx, outTrx);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case ACTION_FLG_DELETE: {
			lRc = deletePanelMtrlRecord(inTrx, outTrx);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case ACTION_FLG_LIST_WHOLE: {
			lRc = listPanelMaterial(outTrx);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case 'P' :
			lRc = pickMaterial(inTrx,outTrx,gEvtTimestamp);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
			
		case 'G' :case 'S' :{
			lRc = changeStatBypick(inTrx, outTrx,gEvtTimestamp);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case 'H' :{
			lRc = changeStatByRwhouse(inTrx, outTrx,gEvtTimestamp);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case ACTION_FLG_FIFO :{//FIFO
			lRc = getMtrlBoxInfoByFIFO(inTrx, outTrx,gEvtTimestamp);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		case 'M' :{
			lRc = queryByDense(inTrx,outTrx);
			if (lRc != _NORMAL) {
				return lRc;
			}
			break;
		}
		default: {
			outTrx.setRtn_mesg("Invalide type input: [" + actionType + "]");
			return E_FPPWHSOPE_INVALID_INPUT;
		}
		}

		return lRc;
	}

	private long pickMaterial(FppwhsopeI inTrx, FppwhsopeO outTrx, Timestamp gEvtTimestamp) {
		long lRc = _NORMAL;
		String gszMsgBuf = "";
		List<Ret_prd_in> retPrdInList = new ArrayList<Ret_prd_in>();
		for(FppwhsopeIA item : inTrx.getIary()){
			Ret_prd_in	prdIn = retPrdInDao.get(item.getMtrl_box_id());
			if (null == prdIn) {
				gszMsgBuf = "箱号为[" + item.getMtrl_box_id() + "]的箱子不存在!";
				outTrx.setRtn_mesg(gszMsgBuf);
				return E_RET_CUS_PRD_IN + E_DELETE_NOT_FOUND;
			}

			if (!DENSE_STAT_IN_PROCESS.equals(prdIn.getPpbox_stat())) {
				gszMsgBuf = "Densebox [" + item.getMtrl_box_id() + "] 的状态无法进行此操作 ！";
				outTrx.setRtn_mesg(gszMsgBuf);
				return E_FPPWHSOPE_INVALID_BOX_STAT;
			}

			String hql = "FROM Ret_prd_in where mtrl_prod_id_fk='"+prdIn.getMtrl_prod_id_fk()+"' and cus_id_fk='"+prdIn.getCus_id_fk()
			+"' and mtrl_thickness="+prdIn.getMtrl_thickness()+" and ppbox_stat='IN' AND PRD_ACT_QTY>0 order by crt_timestamp";
			List<Ret_prd_in> ret_prd_ins = retPrdInDao.find(hql);
			if(ret_prd_ins == null || ret_prd_ins.size() ==0 ){
				gszMsgBuf = "没有符合领料条件的箱子，请确认!";
				outTrx.setRtn_mesg(gszMsgBuf);
				return E_FPPWHSOPE_INVALID_INPUT;
			}

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			//获得符合条件的箱子里操作时间最早的时间
//			Date firstDate = ret_prd_ins.stream().sorted(Comparator.comparing(Ret_prd_in::getCrt_timestamp)).findFirst().map(s -> new Date(s.getCrt_timestamp().getTime())).orElse(null);
			Date firstDate = new Date(ret_prd_ins.get(0).getCrt_timestamp().getTime());
			Date prdInDate = new Date();
			if(prdIn.getCrt_timestamp() != null){
				prdInDate = new Date(prdIn.getCrt_timestamp().getTime());
			}
			String firstDateStr = sdf.format(firstDate);
			String prdInDateStr = sdf.format(prdInDate);
			try {
				firstDate = sdf.parse(firstDateStr);
				prdInDate =sdf.parse(prdInDateStr);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if(prdInDate.after(firstDate)){
				gszMsgBuf = "没有符合先进先出的条件，请确认!";
				outTrx.setRtn_mesg(gszMsgBuf);
				return E_FPPWHSOPE_INVALID_INPUT;
			}
				retPrdInList.add(prdIn);
			}
			for (Ret_prd_in retPrdIn : retPrdInList) {
				retPrdIn.setPpbox_stat(DENSE_STAT_OUT);
				retPrdIn.setEvt_cate(EVT_DENSEBOX_GI);
				retPrdIn.setEvt_usr(inTrx.getEvt_user());
				retPrdIn.setEvt_timestamp(gEvtTimestamp);
				retPrdIn.setBox_weight(0D);
				retPrdInDao.saveOrUpdate(retPrdIn);
			}
		return lRc;
	}

	private long getMtrlBoxInfoByFIFO(FppwhsopeI inTrx, FppwhsopeO outTrx, Timestamp gEvtTimestamp) {
		long lRc = _NORMAL;
		String gszMsgBuf="";
		List<FppwhsopeOA> oList = new ArrayList<FppwhsopeOA>();
		String mtrl_box_id = inTrx.getIary().get(0).getMtrl_box_id();
		if(mtrl_box_id == null || mtrl_box_id.equals(_SPACE)){
			gszMsgBuf = "箱号为空，请确认!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_INVALID_INPUT;
		}
		Ret_prd_in ret_prd_in = retPrdInDao.get(mtrl_box_id);
		/**
		 * Check Conditions
		 */
		if(ret_prd_in == null){
			gszMsgBuf = "箱号为[" + mtrl_box_id + "]的箱子不存在!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_RET_PRD_IN + E_READ_NOT_FOUND;
		}
		if(!ret_prd_in.getBox_cate().trim().equals(DENSEBOX_CATE)){
			gszMsgBuf = "箱子[" + mtrl_box_id + "]的类型是["+ret_prd_in.getBox_cate()+"]，而不是["+DENSEBOX_CATE+"]!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_BOX_CATE_INVALID;
		}
		if(ret_prd_in.getPpbox_stat().equals("COMP") || ret_prd_in.getPrd_act_qty()<=0){
			gszMsgBuf = "箱子[" + mtrl_box_id + "]已经解封完毕，请确认!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_INVALID_BOX_STAT;
		}else if( !ret_prd_in.getPpbox_stat().equals("OUT")){
			gszMsgBuf = "箱子[" + mtrl_box_id + "]不是[出库]状态，请确认!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_INVALID_BOX_STAT;
		}
		/**
		 * 根据FIFO查询Mtrl Box
		 */
		String hql = "FROM Ret_prd_in where mtrl_prod_id_fk='"+ret_prd_in.getMtrl_prod_id_fk()+"' and cus_id_fk='"+ret_prd_in.getCus_id_fk()
					+"' and mtrl_thickness="+ret_prd_in.getMtrl_thickness()+" and ppbox_stat='OUT' AND PRD_ACT_QTY>0";
		List<Ret_prd_in> ret_prd_ins = retPrdInDao.find(hql);
		if(ret_prd_ins == null || ret_prd_ins.size() ==0 ){
			gszMsgBuf = "没有符合解封条件的箱子，请确认!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_INVALID_INPUT;
		}
		if(!ret_prd_ins.get(0).getMtrl_box_id().trim().equals(mtrl_box_id.trim())){
			gszMsgBuf = "箱子["+mtrl_box_id+"]，不是最先入库的，而箱子["+ret_prd_ins.get(0).getMtrl_box_id().trim()
						+"]还有未解封的玻璃，请确认!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_MTRL_BOX_FIFO;
		}
		/**
		 * Set Mtrl Box Information
		 */
		FppwhsopeOA oa = new FppwhsopeOA();
		oa.setMtrl_box_id(ret_prd_in.getMtrl_box_id());
		oa.setCus_id_fk(ret_prd_in.getCus_id_fk());
		oa.setMtrl_lot_id(ret_prd_in.getMtrl_lot_id());
		oa.setMtrl_prod_id_fk(ret_prd_in.getMtrl_prod_id_fk());
		oa.setMtrl_thickness(ret_prd_in.getMtrl_thickness().toString());
		oa.setPpbox_stat(ret_prd_in.getPpbox_stat());
		oa.setPrd_tl_qty(ret_prd_in.getPrd_tl_qty().toString());
		oa.setStb_shop(ret_prd_in.getStb_shop());
		oa.setCrt_timestamp(ret_prd_in.getCrt_timestamp().toString());
		oa.setPrd_act_qty(ret_prd_in.getPrd_act_qty().toString());
		oList.add(oa);
		outTrx.setBox_cnt(1);
		outTrx.setOary(oList);
		return lRc;
	}

	private long changeStatByRwhouse(FppwhsopeI inTrx, FppwhsopeO outTrx, Timestamp gEvtTimestamp) {
		long lRc = _NORMAL;
		String gszMsgBuf = "";
		List<FppwhsopeOA> oaList = new ArrayList<FppwhsopeOA>();
		List<Ret_prd_in> ret_prd_ins = new ArrayList<Ret_prd_in>();
		
		if (inTrx.getBox_cnt() > 0) {
			for(FppwhsopeIA item : inTrx.getIary()){
				Ret_prd_in ret_prd_in = new Ret_prd_in();
				ret_prd_in = retPrdInDao.get(item.getMtrl_box_id());
				if (null == ret_prd_in) {
					gszMsgBuf = "箱号为[" + item.getMtrl_box_id() + "]的箱子不存在!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_RET_PRD_IN + E_READ_NOT_FOUND;
				}
				if (!DENSE_STAT_OUT.equals(ret_prd_in.getPpbox_stat())) {
					gszMsgBuf = "箱子 [" + item.getMtrl_box_id() + "] 状态不正确 ["
							+ ret_prd_in.getPpbox_stat()
							+ "]，不允许进行此操作!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_FPPWHSOPE_INVALID_BOX_STAT;
				}
				ret_prd_ins.add(ret_prd_in);
			}
			for (Ret_prd_in retPrdIn : ret_prd_ins) {
				retPrdIn.setPpbox_stat(DENSE_STAT_IN_PROCESS);
				retPrdIn.setEvt_cate(EVT_DENSEBOX_RETURN_TO_WH);
				retPrdIn.setEvt_usr(inTrx.getEvt_user());
				retPrdIn.setEvt_timestamp(gEvtTimestamp);
				retPrdIn.setBox_weight(0D);
				retPrdInDao.saveOrUpdate(retPrdIn);
			}
//			outTrx.setBox_cnt(inTrx.getIary().size());
//			outTrx.setOary(oaList);
		}
		return lRc;
	}

	private long changeStatBypick(FppwhsopeI inTrx, FppwhsopeO outTrx, Timestamp gEvtTimestamp) {
		long lRc = _NORMAL;
		String gszMsgBuf = "";
		List<FppwhsopeOA> oaList = new ArrayList<FppwhsopeOA>();
		List<Ret_prd_in> retPrdInList = new ArrayList<Ret_prd_in>();
		
		if (inTrx.getBox_cnt() > 0) {
			for(FppwhsopeIA item : inTrx.getIary()){
				Ret_prd_in ret_prd_in = new Ret_prd_in();
				ret_prd_in = retPrdInDao.get(item.getMtrl_box_id());
				if (null == ret_prd_in) {
					gszMsgBuf = "箱号为[" + item.getMtrl_box_id() + "]的箱子不存在!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_RET_CUS_PRD_IN + E_DELETE_NOT_FOUND;
				}
				if (!DENSE_STAT_IN_PROCESS.equals(ret_prd_in.getPpbox_stat())) {
					gszMsgBuf = "Densebox [" + item.getMtrl_box_id() + "] 的状态无法进行此操作 ！";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_FPPWHSOPE_INVALID_BOX_STAT;
				}
//				if (inTrx.getAction_flg().equals("P")) {
//					ret_prd_in.setPpbox_stat(DENSE_STAT_OUT);
//					ret_prd_in.setEvt_cate(EVT_DENSEBOX_GI);
//				}
				if (inTrx.getAction_flg().equals("G")) {
					ret_prd_in.setPpbox_stat(DENSE_STAT_RTCS);
					ret_prd_in.setEvt_cate(EVT_DENSEBOX_RETURN_TO_CUS);
				}
				if (inTrx.getAction_flg().equals("S")) {
					ret_prd_in.setPpbox_stat(DENSE_STAT_SCRP);
					ret_prd_in.setEvt_cate(EVT_DENSEBOX_SCRAP);
				}
				retPrdInList.add(ret_prd_in);
			}
			for (Ret_prd_in retPrdIn : retPrdInList) {
				retPrdIn.setPpbox_stat(DENSE_STAT_OUT);
				retPrdIn.setEvt_cate(EVT_DENSEBOX_GI);
				retPrdIn.setEvt_usr(inTrx.getEvt_user());
				retPrdIn.setEvt_timestamp(gEvtTimestamp);
				retPrdIn.setBox_weight(0D);
				retPrdInDao.saveOrUpdate(retPrdIn);
			}
//			outTrx.setBox_cnt(inTrx.getIary().size());
//			outTrx.setOary(oaList);
		}
		return lRc;
	}

	private long listPanelMaterial(FppwhsopeO outTrx) {
		// TODO Auto-generated method stub
		return 0;
	}

	private long deletePanelMtrlRecord(FppwhsopeI inTrx, FppwhsopeO outTrx) {
		long lRc = _NORMAL;
		String gszMsgBuf = "";
		List<FppwhsopeOA> oaList = new ArrayList<FppwhsopeOA>();
		
		if (inTrx.getBox_cnt() > 0) {
			for(FppwhsopeIA item : inTrx.getIary()){
				Ret_prd_in ret_prd_in = new Ret_prd_in();
				ret_prd_in = retPrdInDao.get(item.getMtrl_box_id());
				if (null == ret_prd_in) {
					gszMsgBuf = "箱号为[" + item.getMtrl_box_id() + "]的箱子不存在!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_RET_PRD_IN + E_READ_NOT_FOUND;
				}
				if (!DENSE_STAT_IN_PROCESS.equals(ret_prd_in.getPpbox_stat())) {
					gszMsgBuf = "箱子 [" + item.getMtrl_box_id() + "] 状态不正确 ["
							+ ret_prd_in.getPpbox_stat()
							+ "]，不允许删除!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_FPPWHSOPE_INVALID_BOX_STAT;
				}
//				ret_prd_in.setEvt_cate(EVT_MTRL_IN_CANCEL);
				retPrdInDao.delete(ret_prd_in);
			}
			outTrx.setBox_cnt(inTrx.getIary().size());
			outTrx.setOary(oaList);
		}
		return lRc;
	}

	private long queryPanelMaterial(FppwhsopeI inTrx, FppwhsopeO outTrx) {
		long lRc = _NORMAL;
		Ret_prd_in ret_prd_in = new Ret_prd_in();
		List<Ret_prd_in> ret_prd_inList = new ArrayList<Ret_prd_in>();
		String hqlQuery = "FROM Ret_prd_in B where 1=1 ";
		if (null != inTrx.getIary()) {
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getMtrl_box_id())) {
				hqlQuery += " AND B.mtrl_box_id = '"
						+ inTrx.getIary().get(0).getMtrl_box_id() + "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getMtrl_thickness())) {
				hqlQuery += " AND B.mtrl_thickness = "
						+ inTrx.getIary().get(0).getMtrl_thickness() + " ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getPrd_tl_qty())) {
				hqlQuery += " AND B.prd_tl_qty = '"
						+ inTrx.getIary().get(0).getPrd_tl_qty() + "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getMtrl_lot_id())) {
				hqlQuery += " AND B.mtrl_lot_id = '"
						+ inTrx.getIary().get(0).getMtrl_lot_id() + "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getCus_id_fk())) {
				hqlQuery += " AND B.cus_id_fk = '"
						+ inTrx.getIary().get(0).getCus_id_fk() + "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getMtrl_prod_id_fk())) {
				hqlQuery += " AND B.mtrl_prod_id_fk = '"
						+ inTrx.getIary().get(0).getMtrl_prod_id_fk() + "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getPpbox_stat())) {
				hqlQuery += " AND B.ppbox_stat = '"
						+ inTrx.getIary().get(0).getPpbox_stat() + "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getStb_shop())) {
				hqlQuery += " AND B.stb_shop = '"
						+ inTrx.getIary().get(0).getStb_shop()+ "' ";
			}
			if (!Util.isSpaceCheck(inTrx.getIary().get(0).getStorage_loc())) {
				hqlQuery += " AND B.storage_loc = '"
						+ inTrx.getIary().get(0).getStorage_loc()+ "' ";
			}
			hqlQuery += "order by crt_timestamp";
			ret_prd_inList = retPrdInDao.find(hqlQuery);

		}
		List<FppwhsopeOA> oaList = new ArrayList<FppwhsopeOA>();
		if (!ret_prd_inList.isEmpty()) {
			outTrx.setBox_cnt(ret_prd_inList.size());
			for (Ret_prd_in item : ret_prd_inList) {
				FppwhsopeOA oa = new FppwhsopeOA();
				oa.setMtrl_box_id(item.getMtrl_box_id());
				oa.setCus_id_fk(item.getCus_id_fk());
				oa.setMtrl_lot_id(item.getMtrl_lot_id());
				oa.setMtrl_prod_id_fk(item.getMtrl_prod_id_fk());
				oa.setMtrl_thickness(item.getMtrl_thickness().toString());
				oa.setPpbox_stat(item.getPpbox_stat());
				oa.setPrd_tl_qty(item.getPrd_tl_qty().toString());
				oa.setPrd_proc_qty(item.getPrd_proc_qty().toString());
				oa.setPrd_act_qty(item.getPrd_act_qty().toString());
				oa.setStorage_loc(item.getStorage_loc());
				oa.setStb_shop(item.getStb_shop());
				oa.setCrt_timestamp(item.getCrt_timestamp().toString());
				oa.setPrd_act_qty(item.getPrd_act_qty().toString());
				oaList.add(oa);
			}
		} else {
			outTrx.setBox_cnt(0);
		}
		outTrx.setOary(oaList);
		return lRc;
	}

	private long updatePanelMtrlRecord(FppwhsopeI inTrx, FppwhsopeO outTrx, Timestamp gEvtTimestamp) {

		long lRc = _NORMAL;
		String gszMsgBuf = "";

		Ret_prd_in ret_prd_in ;
		FppwhsopeIA iary = inTrx.getIary().get(0);

		ret_prd_in = retPrdInDao.get(iary.getMtrl_box_id());
		if (null == ret_prd_in) {
			gszMsgBuf = "箱号为 [" + iary.getMtrl_box_id() + "]的箱子不存在，不允许更新!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_FPPWHSOPE_INVALID_BOX_STAT;
		}

		if (!DENSE_STAT_IN_PROCESS.equals(ret_prd_in.getPpbox_stat())) {
			gszMsgBuf = "箱子 [" + iary.getMtrl_box_id() + "] 状态为： ["
					+ ret_prd_in.getPpbox_stat() + "]，不能修改!";
			outTrx.setRtn_mesg(gszMsgBuf);
			return E_RET_CUS_PRD_IN ;
		}
		ret_prd_in.setCus_id_fk(iary.getCus_id_fk());
		ret_prd_in.setMtrl_lot_id(iary.getMtrl_lot_id());
		ret_prd_in.setMtrl_thickness(Double.parseDouble(iary.getMtrl_thickness()));
//		ret_prd_in.setPpbox_stat(iary.getPpbox_stat());
		ret_prd_in.setStb_shop(iary.getStb_shop());
		ret_prd_in.setStorage_loc(iary.getStorage_loc());
		ret_prd_in.setPrd_tl_qty(Integer.parseInt(iary.getPrd_tl_qty()));
		ret_prd_in.setMtrl_prod_id_fk(iary.getMtrl_prod_id_fk());
		ret_prd_in.setEvt_usr(inTrx.getEvt_user());
		ret_prd_in.setBox_weight(0D);
		retPrdInDao.saveOrUpdate(ret_prd_in);

		return lRc;
		}
	
	/**
	 * Add mtrl box info
	 * 
	 * @return
	 */
	private long panelMaterialReceive(FppwhsopeI inTrx, FppwhsopeO outTrx, Timestamp gEvtTimestamp) {
		long lRc = _NORMAL;
			String gszMsgBuf = "";
			SimpleRtnBeen rtnBeen = new SimpleRtnBeen(_NORMAL);
			List<Ret_prd_in> ret_prd_inList = new ArrayList<Ret_prd_in>();
			List<FppwhsopeIA> iaryList = inTrx.getIary();
			List<FppwhsopeOA> oaList = new ArrayList<FppwhsopeOA>();
			for (FppwhsopeIA iary : iaryList) {
				Ret_prd_in ret_prd_in = retPrdInDao.get(iary.getMtrl_box_id());
				if (null != ret_prd_in) {
					gszMsgBuf = "箱子已经存在!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_FPPWHSOPE + E_ADD_EXIST;
				}else if( null == bisMtrlDao.get(iary.getMtrl_prod_id_fk())){
					gszMsgBuf = "来料料号不存在!";
					outTrx.setRtn_mesg(gszMsgBuf);
					return E_FPPWHSOPE_BISMTRL_NOT_FOUND;
				}
			}
			String lot_id = this.createlotId(inTrx);
			for (FppwhsopeIA iary : iaryList) {
				
				Ret_prd_in ret_prd_in = new Ret_prd_in();
				ret_prd_in.setMtrl_box_id(iary.getMtrl_box_id());
				ret_prd_in.setCus_id_fk(iary.getCus_id_fk());
				ret_prd_in.setMtrl_lot_id(lot_id);
				ret_prd_in.setMtrl_thickness(Double.parseDouble(iary.getMtrl_thickness()));
				ret_prd_in.setBox_cate(DENSEBOX_CATE);
				ret_prd_in.setPpbox_stat(DENSE_STAT_IN_PROCESS);
				ret_prd_in.setStb_shop(iary.getStb_shop());
				ret_prd_in.setStorage_loc(iary.getStorage_loc());
				ret_prd_in.setPrd_tl_qty(Integer.parseInt(iary.getPrd_tl_qty()));
				ret_prd_in.setMtrl_prod_id_fk(iary.getMtrl_prod_id_fk());
				ret_prd_in.setEvt_cate(EVT_DENSEBOX_GI);
				ret_prd_in.setCrt_usr(inTrx.getEvt_user());
				ret_prd_in.setCrt_timestamp(gEvtTimestamp);
				ret_prd_in.setBox_weight(0.0);
				ret_prd_in.setPrd_act_qty(Integer.parseInt(iary.getPrd_tl_qty()));
				ret_prd_in.setPrd_proc_qty(0);
				retPrdInDao.save(ret_prd_in);
			}
				return lRc;
	}
	 public  String getStringDate() {  
         Date currentTime = new Date();  
         SimpleDateFormat formatter = new SimpleDateFormat("yyMMdd");  
         String dateString = formatter.format(currentTime);  
         return dateString;  
      }  
	 private String createlotId(FppwhsopeI inTrx){

			String lot_id="";
			Ret_crt_no ret_crt_no = new Ret_crt_no();
			Ret_crt_noId id = new Ret_crt_noId();
			String key_id = this.getStringDate();
			id.setType(LOT_TYPE);
			id.setKey_id(key_id);
			ret_crt_no = retCrtNoDao.get(id);
			if(ret_crt_no == null){
				Ret_crt_no ret_crt_no_new = new Ret_crt_no();
				ret_crt_no_new.setId(id);
				String serial_no = String.format("%03d", 1);
				ret_crt_no_new.setSerial_no(serial_no);
				ret_crt_no_new.setEvt_usr(inTrx.getEvt_user());
				ret_crt_no_new.setEvt_timestamp(Util.getCurrentTimestamp());
				lot_id = key_id + "001";
				retCrtNoDao.save(ret_crt_no_new);
			}else{
				String old_serial_no = ret_crt_no.getSerial_no();
				int old_serial_no_int = Integer.valueOf(old_serial_no);
				int new_serial_no_int = old_serial_no_int + 1;
				String new_serial_no = String.format("%03d", new_serial_no_int);
				ret_crt_no.setSerial_no(new_serial_no);
				ret_crt_no.setEvt_usr(inTrx.getEvt_user());
				ret_crt_no.setEvt_timestamp(Util.getCurrentTimestamp());
				retCrtNoDao.update(ret_crt_no);
				lot_id = key_id + new_serial_no;
			}
			return lot_id;
	 }
	 
	 public long queryByDense(FppwhsopeI inTrx, FppwhsopeO outTrx){
		 	long lRc = _NORMAL;
			List<FppwhsopeIA> iary=inTrx.getIary();
			String dense_id = iary.get(0).getMtrl_box_id();
			Ret_prd_in ret_prd_in = retPrdInDao.get(dense_id);
			if (ret_prd_in == null) {
				outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR+"");
				outTrx.setRtn_mesg("dense is null");
				return E_FPPWHSOPE; 
			}
			if (Util.isSpaceCheck(ret_prd_in.getMtrl_prod_id_fk())) {
				outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR+"");
				outTrx.setRtn_mesg("dense mtrl_prod_id_fk is null");
				return E_FPPWHSOPE; 
			}
			if (Util.isSpaceCheck(ret_prd_in.getCus_id_fk())) {
				outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR+"");
				outTrx.setRtn_mesg("dense Cus_id_fk is null");
				return E_FPPWHSOPE;
			}
			if (Util.isSpaceCheck(String.valueOf(ret_prd_in.getMtrl_thickness()))) {
				outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR+"");
				outTrx.setRtn_mesg("dense mtrl_thickness is null");
				return E_FPPWHSOPE;
			}
			if (ret_prd_in.getPrd_act_qty() <=0) {
				outTrx.setRtn_code(E_FDPAPLYWO_OTHER_ERROR+"");
				outTrx.setRtn_mesg("dense prd_act_qty is null");
				return E_FPPWHSOPE;
			}
			Bis_mtrl bis_mtrl = bisMtrlDao.get(ret_prd_in.getMtrl_prod_id_fk());
			List<FppwhsopeOB> oaryList = new ArrayList<FppwhsopeOB>();
			String hql = "FROM Ret_wo where wo_typ='W' and mtrl_prod_id_fk = '"+ret_prd_in.getMtrl_prod_id_fk()+
					"' and from_thickness="+ret_prd_in.getMtrl_thickness()+" and pln_prd_qty> rl_prd_qty order by pln_stb_timestamp";

			List<Ret_wo> ret_wos=retWODao.find(hql);
			
			if(null != ret_wos && ret_wos.size()>0){
				String hql_prty = "FROM Ret_wo where wo_typ='W' and mtrl_prod_id_fk = '"+ret_prd_in.getMtrl_prod_id_fk()+
						"' and from_thickness="+ret_prd_in.getMtrl_thickness()+" and pln_prd_qty> rl_prd_qty "
								+ "and pln_stb_timestamp ='"+ ret_wos.get(0).getPln_stb_timestamp()+"' order by wo_prty";
				List<Ret_wo> ret_wo_prty=retWODao.find(hql_prty);
				if(null != ret_wo_prty && ret_wo_prty.size()>0){
					FppwhsopeOB fppwhsopeOB = new FppwhsopeOB();
					fppwhsopeOB.setWo_id(ret_wos.get(0).getWo_id());
					fppwhsopeOB.setMdl_id_fk(ret_wos.get(0).getMdl_id_fk());
					fppwhsopeOB.setPln_prd_qty(ret_wos.get(0).getPln_prd_qty());
					fppwhsopeOB.setRl_prd_qty(ret_wos.get(0).getRl_prd_qty());
					fppwhsopeOB.setMtrl_box_id(ret_prd_in.getMtrl_box_id());
					fppwhsopeOB.setPrd_act_qty(ret_prd_in.getPrd_act_qty());
					fppwhsopeOB.setPrd_proc_qty(ret_prd_in.getPrd_proc_qty());
					fppwhsopeOB.setPrd_tl_qty(ret_prd_in.getPrd_tl_qty());
					fppwhsopeOB.setPrd_act_qty(ret_prd_in.getPrd_act_qty());
					fppwhsopeOB.setMtrl_prod_id_fk(ret_prd_in.getMtrl_prod_id_fk());
					fppwhsopeOB.setCus_id_fk(ret_prd_in.getCus_id_fk());
					fppwhsopeOB.setMtrl_thickness(ret_prd_in.getMtrl_thickness());
					fppwhsopeOB.setMtrl_cate(bis_mtrl.getMtrl_cate());
					oaryList.add(fppwhsopeOB);
					}
				}
			outTrx.setOaryB(oaryList);
			outTrx.setRtn_mesg(RETURN_MESG_OK);
			return lRc;
		}
}
